package org.acooke.uykfd.db

import java.io.File
import java.sql.Connection
import java.util.regex.Pattern

import org.apache.empire.db.DBColumnExpr
import org.apache.empire.db.DBCommand
import org.apache.empire.db.DBJoinType
import org.apache.empire.db.DBQuery
import org.apache.empire.db.DBReader

import scala.util.Random


trait ArtistCanonicalRow {

  def artistId: Int
  def artist(cnxn: Connection): ArtistRow
  def canonicalId: Int
  def canonical(cnxn: Connection): CanonicalRow

}

trait ArtistCanonicalTable
  extends Table[ArtistCanonicalTable, Schema.ArtistCanonical] {

  def fromBoth(artist: ArtistRow, canonical: CanonicalRow, cnxn: Connection): ArtistCanonicalRow
  def fromArtist(artist: ArtistRow, cnxn: Connection): List[ArtistCanonicalRow]
  def fromArtist(artistId: Int, cnxn: Connection): List[ArtistCanonicalRow]

}

private class ArtistCanonicalFactory
  extends Factory[ArtistCanonicalRecord, ArtistCanonicalTable, Schema.ArtistCanonical](
    Schema.ARTIST_CANONICAL) 
  with ArtistCanonicalTable {

  def rowFactory = new ArtistCanonicalRecord()

  def fromBoth(artist: ArtistRow, canonical: CanonicalRow, cnxn: Connection) = {
    val cmd = allWhere(table.ARTIST.is(artist.id))
    cmd.where(table.CANONICAL.is(canonical.id))
    val row = rowFactory
    row.pull(cmd, cnxn, (x: Exception) => {row.pushBoth(artist, canonical, cnxn); row})
  }

  def fromArtist(artist: ArtistRow, cnxn: Connection) = fromArtist(artist.id, cnxn)

  def fromArtist(artistId: Int, cnxn: Connection) = 
    buildList(allWhere(table.ARTIST.is(artistId)), cnxn)

}

private class ArtistCanonicalRecord
  extends Record[ArtistCanonicalRecord, ArtistCanonicalTable, Schema.ArtistCanonical](
    Schema.ARTIST_CANONICAL) 
  with ArtistCanonicalRow {

  def pushBoth(artist: ArtistRow, canonical: CanonicalRow, cnxn: Connection) {
    set(table.ARTIST, artist.id)
    set(table.CANONICAL, canonical.id)
    update(cnxn)
  }

  def artistId = asInt(table.ARTIST)
  def artist(cnxn: Connection) = null
  def canonicalId = asInt(table.CANONICAL)
  def canonical(cnxn: Connection) = Schema.CANONICALS.orm.fromId(canonicalId, cnxn)

}


trait ArtistRow extends IdValueRow {

  val parens = Pattern.compile("""\([^\)]*\)""")
  val possessive = Pattern.compile("'s.*")

}

trait ArtistTable
  extends IdValueTable[ArtistRow, ArtistTable, Schema.Artists] {

  def fromValue(value: String, cnxn: Connection): ArtistRow
  def maxArtistId(cnxn: Connection): Int
  def random(cnxn: Connection): ArtistRow
}

private class ArtistFactory
  extends IdValueFactory[ArtistRecord, ArtistTable, Schema.Artists](Schema.ARTISTS)
  with ArtistTable {

  override protected def rowFactory = new ArtistRecord

  private val random = Random

  def maxArtistId(cnxn: Connection): Int = {
    val cmd = Schema.createCommand
    cmd.select(table.ID.max)
    Schema.querySingleInt(cmd.getSelect, 0, cnxn)
  }

  def random(cnxn: Connection): ArtistRow = {
    val n = maxArtistId(cnxn)
    def read: ArtistRow =
      try {
        fromId(1 + random.nextInt(n), cnxn)
      } catch {
        case e: Exception => read
      }
    read
  }

}

private class ArtistRecord
  extends IdValueRecord[ArtistRecord, ArtistTable, Schema.Artists](Schema.ARTISTS)
  with ArtistRow {

  val acOrm = Schema.ARTIST_CANONICAL.orm
  val cOrm = Schema.CANONICALS.orm

  override def pushValue(value: String, cnxn: Connection) = {
    val ok = super.pushValue(value, cnxn)

    // because we don't add duplicates we can "go wild" here trying different combinations
    // the only downside is that we may get bad connections between artists

    val spaces = List("[,\\.\\-/ \\\\]", "[,/ \\\\]")

    for (space <- spaces) {

      // entire string
      acOrm.fromBoth(this, cOrm.fromValue(value, cnxn, ignoreStart=false, toSpace=space), cnxn)

      // separated
      for (artist <- cOrm.split(value)) 
        acOrm.fromBoth(this, cOrm.fromValue(artist, cnxn, ignoreStart=true, toSpace=space), cnxn)

      // and repeat without parens
      val matchParens = parens.matcher(value)
      val noParens = matchParens.replaceAll(" ")
      if (noParens != value) {
        acOrm.fromBoth(this, cOrm.fromValue(noParens, cnxn, ignoreStart=false, toSpace=space), cnxn)
        for (artist <- cOrm.split(noParens))
          acOrm.fromBoth(this, cOrm.fromValue(artist, cnxn, ignoreStart=true, toSpace=space), cnxn)    
      }

      // and repeat without 's
      val matchPossessive = possessive.matcher(value)
      val noPossessive = matchPossessive.replaceAll(" ")
      if (noPossessive != value && noPossessive != noParens) {
        acOrm.fromBoth(this, cOrm.fromValue(noPossessive, cnxn, ignoreStart=false, toSpace=space), cnxn)
        for (artist <- cOrm.split(noPossessive))
          acOrm.fromBoth(this, cOrm.fromValue(artist, cnxn, ignoreStart=true, toSpace=space), cnxn)    
      }

    }

    ok
  }

  def canonicals(cnxn: Connection) = {
    for (ac <- acOrm.fromArtist(this, cnxn)) 
      yield (ac.canonical(cnxn))
  }

}

/**
 * Support for a table that stores both a string VALUE and a
 * reference to a canonical version of the string.
 */
trait IdValueCanonicalRow extends IdValueRow {

  def canonical(cnxn: Connection): CanonicalRow

}

trait IdValueCanonicalTable[R <: IdValueCanonicalRow, F, T <: Schema.IdValueCanonical[F]]
  extends IdValueTable[R,F,T]

private abstract class IdValueCanonicalFactory[
    R <: IdValueCanonicalRecord[R,F,T], F, T <: Schema.IdValueCanonical[F]](table: => T)
  extends IdValueFactory[R,F,T](table)

private class IdValueCanonicalRecord[
    S <: IdValueCanonicalRecord[S,F,T], F, T <: Schema.IdValueCanonical[F]](table: => T)
  extends IdValueRecord[S,F,T](table) 
  with IdValueCanonicalRow {

  self: S =>

  def canonical_id = asInt(table.CANONICAL)
  def canonical(cnxn: Connection) = Schema.CANONICALS.orm.fromId(canonical_id, cnxn)

  override def pushValue(value: String, cnxn: Connection) = {
    set(table.VALUE, value)
    push(table.CANONICAL, Schema.CANONICALS.orm.fromValue(value, cnxn).id, cnxn)
  }

}

/**
 * The ALBUMS table.
 */
trait AlbumRow
  extends IdValueCanonicalRow

trait AlbumTable
  extends IdValueCanonicalTable[AlbumRow, AlbumTable, Schema.Albums] {

  def fromValue(value: String, cnxn: Connection): AlbumRow
}

private class AlbumFactory
  extends IdValueCanonicalFactory[AlbumRecord, AlbumTable, Schema.Albums](Schema.ALBUMS)
  with AlbumTable {

  def rowFactory = new AlbumRecord

}

private class AlbumRecord
  extends IdValueCanonicalRecord[AlbumRecord, AlbumTable, Schema.Albums](Schema.ALBUMS)
  with AlbumRow


trait SongTitleRow
  extends IdValueCanonicalRow

trait SongTitleTable
  extends IdValueCanonicalTable[SongTitleRow, SongTitleTable, Schema.SongTitles] {

  def fromValue(value: String, cnxn: Connection): SongTitleRow
}

private class SongTitleFactory
  extends IdValueCanonicalFactory[SongTitleRecord, SongTitleTable, Schema.SongTitles](Schema.SONG_TITLES)
  with SongTitleTable {

  def rowFactory = new SongTitleRecord

}

private class SongTitleRecord
  extends IdValueCanonicalRecord[SongTitleRecord, SongTitleTable, Schema.SongTitles](Schema.SONG_TITLES)
  with SongTitleRow


trait TrackRow
  extends IdRow {

  def artistId: Int
  def artist(cnxn: Connection): ArtistRow
  def albumId: Int
  def album(cnxn: Connection): AlbumRow
  def songTitleId: Int
  def songTitle(cnxn: Connection): SongTitleRow
  def trackNumber: Int
  def path: String

}

trait TrackTable
  extends IdTable[TrackRow, TrackTable, Schema.Tracks] {

  def fromTags(artist: String, album: String, songTitle: String, trackNumber: Int, path: String, cnxn: Connection): TrackRow
  def deletePathBelow(file: File, cnxn:Connection)
  def clean(cnxn: Connection)
  def randomForArtist(artist: ArtistRow, cnxn: Connection): TrackRow
  def countForArtistId(artistId: Int, cnxn: Connection): Int
  def fromArtist(artist: ArtistRow, cnxn: Connection): List[TrackRow]
  def fromArtistId(artist: Int, cnxn: Connection): List[TrackRow]

}

private class TrackFactory
  extends IdFactory[TrackRecord, TrackTable, Schema.Tracks](Schema.TRACKS)
  with TrackTable {

  def rowFactory = new TrackRecord
  val random = new Random

  def fromTags(artist: String, album: String, songTitle: String, trackNumber: Int, path: String, cnxn: Connection) = {
    assert(artist != null && ! artist.equals(""), "No artist.")
    assert(album != null && ! album.equals(""), "No album.")
    assert(songTitle != null && ! songTitle.equals(""), "No song title.")
    assert(path != null && ! path.equals(""), "No path.")
    val artistRow = Schema.ARTISTS.orm.fromValue(artist, cnxn)
    val albumRow = Schema.ALBUMS.orm.fromValue(album, cnxn)
    val songTitleRow = Schema.SONG_TITLES.orm.fromValue(songTitle, cnxn)
    val cmd = allWhere(table.ARTIST.is(artistRow.id))
    cmd.where(table.ALBUM.is(albumRow.id))
    cmd.where(table.SONG_TITLE.is(songTitleRow.id))
    cmd.where(table.TRACK_NUMBER.is(trackNumber))
    cmd.where(table.PATH.is(path))
    val row = rowFactory
    row.pull(cmd, cnxn, (x: Exception) => row.pushTags(artistRow, albumRow, songTitleRow, trackNumber, path, cnxn))
  }

  def deletePathBelow(file: File, cnxn:Connection) {
    if (! file.isDirectory()) throw new Exception(file.getPath + " is not a directory")
    val cmd = where(table.PATH.like(file.getCanonicalPath() + File.separator + "%"))
    Schema.executeSQL(cmd.getDelete(table), cnxn)
  }

  def clean(cnxn: Connection) {
    def cleanOrphans(column: DBColumnExpr, orphans: Schema.Id[_]) {
      val orphanIds = Schema.createCommand
      orphanIds.select(orphans.ID)
      orphanIds.join(column, orphans.ID, DBJoinType.RIGHT)
      orphanIds.where(table.ID.is(null))
      val cmd = where(orphans.ID.in(new DBQuery(orphanIds)))
      Schema.executeSQL(cmd.getDelete(orphans), cnxn)
    }
    // this fails with mysql so drop it for now
//    cleanOrphans(Schema.TRACKS.ARTIST, Schema.ARTISTS)
    // TODO - artist canonical orphans
//    cleanOrphans(Schema.TRACKS.ALBUM, Schema.ALBUMS)
//    cleanOrphans(Schema.TRACKS.SONG_TITLE, Schema.SONG_TITLES)
  }

  def randomForArtist(artist: ArtistRow, cnxn: Connection) = {
    val cmd = where(Schema.TRACKS.ARTIST.is(artist.id))
    cmd.select(Schema.TRACKS.ID)
    val ids = buildList(cmd, Schema.TRACKS.ID, id, cnxn)
    fromId(ids(random.nextInt(ids.length)), cnxn)
  }

  def countForArtistId(artistId: Int, cnxn: Connection) = {
    val cmd = where(Schema.TRACKS.ARTIST.is(artistId))
    cmd.select(table.count)
    Schema.querySingleInt(cmd.getSelect, 0, cnxn)    
  }

  def fromArtist(artist: ArtistRow, cnxn: Connection) = fromArtistId(artist.id, cnxn)

  def fromArtistId(artistId: Int, cnxn: Connection) = {
    val cmd = where(table.ARTIST.is(artistId))
    cmd.select(table.ID)
    buildList(cmd, table.ID, fromId(_, cnxn), cnxn)
  }

}

private class TrackRecord
  extends IdRecord[TrackRecord, TrackTable, Schema.Tracks](Schema.TRACKS)
  with TrackRow {

  def artistId = asInt(table.ARTIST)
  def artist(cnxn: Connection) = Schema.ARTISTS.orm.fromId(artistId, cnxn)
  def albumId = asInt(table.ALBUM)
  def album(cnxn: Connection) = Schema.ALBUMS.orm.fromId(albumId, cnxn)
  def songTitleId = asInt(table.SONG_TITLE)
  def songTitle(cnxn: Connection) = Schema.SONG_TITLES.orm.fromId(songTitleId, cnxn)
  def trackNumber = asInt(table.TRACK_NUMBER)
  def path = asString(table.PATH)

  def pushTags(
    artist: ArtistRow, album: AlbumRow, songTitle: SongTitleRow, trackNumber: Int, path: String, 
    cnxn: Connection
  ): this.type = {
    set(table.ARTIST, artist.id)
    set(table.ALBUM, album.id)
    set(table.SONG_TITLE, songTitle.id)
    set(table.TRACK_NUMBER, trackNumber)
    set(table.PATH, path)
    update(cnxn)
    this
  }

  override def toString = path

}
